package net.stuffrepos.userdesktopdatabase;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import net.stuffrepos.util.cache.CacheableValue;
import net.stuffrepos.util.config.UserConfig;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author Eduardo H. Bogoni <eduardobogoni@gmail.com>
 */
public class Database {

    public static final int LAST_VERSION = -1;
    public static final String DEFAULT_DATABASE = "main";
    private static final String schemaVersionTable = "schema_version";
    private static final Log log = LogFactory.getLog(Database.class);
    private final UserConfig config;
    private final Migration[] migrations;
    private final String database;
    private boolean opened = false;
    private final CacheableValue<File> databasesDirectory = new CacheableValue<File>() {

        @Override
        protected File calculate() {
            return config.getFile("/database");
        }
    };
    private final CacheableValue<String> databasePathPrefix = new CacheableValue<String>() {

        @Override
        protected String calculate() {
            return new File(databasesDirectory.getValue(), database).getAbsolutePath();
        }
    };
    private final CacheableValue<File[]> databaseFiles = new CacheableValue<File[]>() {

        @Override
        protected File[] calculate() {
            return new File[]{
                        new File(databasePathPrefix.getValue() + ".log"),
                        new File(databasePathPrefix.getValue() + ".properties"),
                        new File(databasePathPrefix.getValue() + ".script"),
                        new File(databasePathPrefix.getValue() + ".lock")
                    };
        }
    };

    static {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex);
        }
    }

    public Database(UserConfig config, Migration[] migrations) {
        this(config, migrations, null);
    }

    public Database(UserConfig config, Migration[] migrations, String database) {
        this.config = config;
        this.migrations = migrations;
        this.database = (database == null ? DEFAULT_DATABASE : database);
    }

    public void clearDatabase() {
        for (File file : databaseFiles.getValue()) {
            file.delete();
        }
    }

    public String getConnectionUrl() {
        return new StringBuilder("jdbc:hsqldb:file://").
                append(databasePathPrefix.getValue()).
                append(";shutdown=true").
                append(";hsqldb.write_delay=false").
                toString();
    }

    private void lockDatabase() throws SQLException {
        if (!exists()) {
            log.debug("Database not found");
            createNewDatabase();
        } else {
            log.debug("Database found");
        }
    }

    public void open() throws SQLException {
        open(LAST_VERSION);
    }

    public void close() {
        if (opened) {
            try {
                executeDatabaseOperation(new Operation<Boolean>() {

                    @Override
                    public Boolean execute(Connection connection) throws SQLException {
                        return connection.createStatement().execute("SHUTDOWN");
                    }
                });
            } catch (SQLException ex) {
                log.warn("Exception on shutdown database", ex);
            }
            opened = false;
        }

    }

    private void createNewDatabase() throws SQLException {
        databasesDirectory.getValue().mkdirs();
        executeDatabaseOperation(new Operation<Boolean>() {

            @Override
            public Boolean execute(Connection connection) throws SQLException {
                return connection.createStatement().execute(
                        "create table " + schemaVersionTable + " (number integer not null primary key)");
            }
        });
    }

    public int getCurrentVersion() throws SQLException {
        return executeDatabaseOperation(new Operation<Integer>() {

            @Override
            public Integer execute(Connection connection) throws SQLException {
                ResultSet rs = connection.createStatement().executeQuery("select max(number) from " + schemaVersionTable);
                while (rs.next()) {
                    return rs.getInt(1);
                }
                return 0;
            }
        });
    }

    private void runMigrations(int version) throws SQLException {

        if (!((version == LAST_VERSION) || (version >= 0 && version <= migrations.length))) {
            throw new ArrayIndexOutOfBoundsException(version);
        }

        log.trace("Running migrations");
        log.debug("Migrations found: " + migrations.length);
        log.debug("Current version: " + getCurrentVersion());

        if (version == LAST_VERSION) {
            version = migrations.length;
        }

        while (getCurrentVersion() < version) {
            log.debug("Migration Up SQL Command");
            executeMigrationCommands(migrations[getCurrentVersion()].up());
            setCurrentVersion(getCurrentVersion() + 1);
            log.debug("Database updated to version " + getCurrentVersion());
        }

        while (getCurrentVersion() > version) {
            log.debug("Migration Down SQL Command");
            executeMigrationCommands(migrations[getCurrentVersion() - 1].down());
            setCurrentVersion(getCurrentVersion() - 1);
            log.debug("Database updated to version " + getCurrentVersion());
        }


    }

    private void executeMigrationCommands(String[] sqls) {
        for (final String sql : sqls) {
            log.debug("Migration SQL Command: " + sql);
            try {
                executeDatabaseOperation(new Operation<Boolean>() {

                    @Override
                    public Boolean execute(Connection connection) throws SQLException {
                        return connection.createStatement().execute(sql);
                    }
                });
            } catch (SQLException ex) {
                if (log.isWarnEnabled()) {
                    log.warn("Fail on execute sql \"" + sql + "\"", ex);
                }
            }
        }
    }

    public <ReturnType> ReturnType executeDatabaseOperation(Operation<ReturnType> operation) throws SQLException {
        Connection connection = DriverManager.getConnection(getConnectionUrl());

        try {
            return operation.execute(connection);
        } finally {
            connection.commit();
            connection.close();
        }
    }

    public boolean exists() {
        for (File file : databaseFiles.getValue()) {
            if (file.exists()) {
                return true;
            }
        }
        return false;
    }

    private void setCurrentVersion(final int version) throws SQLException {
        log.debug("Setting version to " + version);
        executeDatabaseOperation(new Operation<Integer>() {

            @Override
            public Integer execute(Connection connection) throws SQLException {
                return connection.createStatement().executeUpdate("delete from " + schemaVersionTable);
            }
        });

        executeDatabaseOperation(new Operation<Integer>() {

            @Override
            public Integer execute(Connection connection) throws SQLException {
                PreparedStatement stmt = connection.prepareStatement(
                        "insert into " + schemaVersionTable + "(number) values(?)");
                stmt.setInt(1, version);

                return stmt.executeUpdate();
            }
        });
    }

    public void open(int version) throws SQLException {
        if (!opened) {
            log.debug("Opening database with URL " + getConnectionUrl());
            log.debug("Requested version: " + version);
            lockDatabase();
            runMigrations(version);
            opened = true;
        }
    }

    public static interface Operation<ReturnType> {

        public ReturnType execute(Connection connection) throws SQLException;
    }
}
